Re: [SQL] Tricky -to me!- SQL query. - Mailing list pgsql-sql

>> Stuart wrote:
>>
>> Consider the following table:
>> dev_brecard=> select * from test order by person;
>> person|fruit
>> ------+---------
>> lucy  |mandarins
>> lucy  |tomatoes
>> lucy  |pears
>> lucy  |oranges
>> lucy  |apples
>> peter |pears
>> peter |apples
>> peter |oranges
>> peter |prunes
>> robert|figs
>> robert|dates
>> stuart|apples
>> stuart|pears
>> stuart|prunes
>> stuart|bananas
>> stuart|kumquats
>> (16 rows)
>>
>> (code for creating and populating table is in a PS at the end of this
>>posting)
>>
>> You can assume that the table is appropriately normalised and that there is
>> a composite primary key for it (i.e. each COMBINATION of person and fruit
>> will appear only once and neither of the fields can be NULL)
>>
>> How do I select from all person who like 'pears' and 'apples' (in this
>> case, lucy, peter and stuart) or 'pears', 'apples' and 'oranges' (in this
>> case, lucy and peter)?
>
> Remigiusz answered:
>
>What about:
>SELECT person FROM test WHERE fruit='pears' AND fruit='apples' AND
>fruit='oranges';
>or just
>SELECT person FROM test WHERE fruit IN ('pears', 'apples','oranges');
>
>For me strange question - so I think I don't mean exactly what you do ;-)

Hello Rem,

Yes, there is a misunderstanding here, sorry about that, could be my posting.

The first solution would return an empty table because there are no cases
where fruit can be equal to two different things (the data in fruit are
atomic so the field fruit can only ever be equal to one thing).

The second of solutions answers the question "Who eats pears or eats apples
or eats oranges?" but not the question "Who eats pears AND apples AND
oranges?" (i.e. it would give the answers lucy, peter and stuart when the
actual answers should be lucy and peter because, in the example table,
stuart does not eat oranges).

>Could You explain for what You wanna this?

Hope this is clearer.

regards,

Stuart.

PS.  Of course I actually quite like oranges ;)

+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+